-- psql projet_4ir
-- psql -f functionsEnseignant.sql projet_4ir
-- \q pour quitter

DROP FUNCTION IF EXISTS getInformationsEnseignant( VARCHAR(50) );
CREATE OR REPLACE FUNCTION getInformationsEnseignant( mail VARCHAR(50) ) RETURNS TABLE(email VARCHAR(50), affectation VARCHAR(10), identifiant VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30)) AS $$
BEGIN
	RETURN QUERY SELECT E.email, D.libelle affectation, E.immatriculation, E.nom, E.prenom FROM Enseignant E, Affectations A, Departement D WHERE E.email = mail AND E.immatriculation = A.enseignant AND D.id_departement = A.departement;	
END;
$$ LANGUAGE plpgsql;

--liste des étudiants qui ont OPERATOR X absences dans sa matiere
DROP FUNCTION IF EXISTS getOPERATORXAbsenceIn(INT, INT) ;
CREATE OR REPLACE FUNCTION getOPERATORXAbsenceIn(param_id_cours INT, param_justification INT) RETURNS TABLE(ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite, nb_absences BIGINT) AS $$
BEGIN
	IF param_justification = 0 THEN --si aucune justification
	     RETURN QUERY SELECT A.ine, E.nom, E.prenom, E.affectation, COUNT(*) nb_absences FROM Absence A, Seance S, Etudiant E WHERE S.id_seance = A.id_seance AND E.ine = A.ine AND A.matiere = S.matiere AND A.isA = S.isA AND A.matiere = param_id_cours AND A.credibilite = 0 GROUP BY A.ine, E.nom, E.prenom, E.affectation;
	ELSIF param_justification = 1 THEN --si seulement justifié
	     RETURN QUERY SELECT A.ine, E.nom, E.prenom, E.affectation, COUNT(*) nb_absences FROM Absence A, Seance S, Etudiant E WHERE S.id_seance = A.id_seance AND E.ine = A.ine AND A.matiere = S.matiere AND A.isA = S.isA AND A.matiere = param_id_cours AND A.credibilite > 0 GROUP BY A.ine, E.nom, E.prenom, E.affectation;
	ELSE --si peu importe
	     RETURN QUERY SELECT A.ine, E.nom, E.prenom, E.affectation, COUNT(*) nb_absences FROM Absence A, Seance S, Etudiant E WHERE S.id_seance = A.id_seance AND E.ine = A.ine AND A.matiere = S.matiere AND A.isA = S.isA AND A.matiere = param_id_cours GROUP BY A.ine, E.nom, E.prenom, E.affectation;
	END IF;
END;
$$ LANGUAGE plpgsql;

-- liste des étudiants qui ne se sont jamais absentés à sa matière
DROP FUNCTION IF EXISTS getNeverAbsentIn(INT);
CREATE OR REPLACE FUNCTION getNeverAbsentIn(param_id_cours INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite) AS $$
BEGIN
	RETURN QUERY SELECT E.ine, E.nom, E.prenom, E.affectation FROM Etudiant E WHERE param_id_cours = ANY(E.setcourses) AND E.ine NOT IN (SELECT DISTINCT A.ine FROM Absence A);
END;
$$ LANGUAGE plpgsql;

--liste des étudiants qui se sont toujours absentés à son cours ID_COURS
DROP FUNCTION IF EXISTS getAlwaysAbsentForEverything(INT, INT);
CREATE OR REPLACE FUNCTION getAlwaysAbsentForEverything(param_id_cours INT, param_justification INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite ) AS $$
BEGIN
	IF param_justification = 0 THEN
	   RETURN QUERY SELECT Q.ine, Q.nom, Q.prenom, Q.affectation FROM (SELECT A.ine, E.nom, E.prenom, E.affectation, A.matiere, COUNT(*) total_absences FROM Absence A, Etudiant E WHERE E.ine = A.ine AND A.matiere = param_id_cours AND A.credibilite = 0 GROUP BY A.ine, E.nom, E.prenom, E.affectation, A.matiere) AS Q INNER JOIN Matiere Mat ON Mat.id_matiere = Q.matiere WHERE Q.total_absences = (Mat.nb_heures_Cours + Mat.nb_heures_Tds + Mat.nb_heures_Tps);
	ELSIF param_justification = 1 THEN
	   RETURN QUERY SELECT Q.ine, Q.nom, Q.prenom, Q.affectation FROM (SELECT A.ine, E.nom, E.prenom, E.affectation, A.matiere, COUNT(*) total_absences FROM Absence A, Etudiant E WHERE E.ine = A.ine AND A.matiere = param_id_cours AND A.credibilite > 0 GROUP BY A.ine, E.nom, E.prenom, E.affectation, A.matiere) AS Q INNER JOIN Matiere Mat ON Mat.id_matiere = Q.matiere WHERE Q.total_absences = (Mat.nb_heures_Cours + Mat.nb_heures_Tds + Mat.nb_heures_Tps);
	ELSE
	   RETURN QUERY SELECT Q.ine, Q.nom, Q.prenom, Q.affectation FROM (SELECT A.ine, E.nom, E.prenom, E.affectation, A.matiere, COUNT(*) total_absences FROM Absence A, Etudiant E WHERE E.ine = A.ine AND A.matiere = param_id_cours GROUP BY A.ine, E.nom, E.prenom, E.affectation, A.matiere) AS Q INNER JOIN Matiere Mat ON Mat.id_matiere = Q.matiere WHERE Q.total_absences = (Mat.nb_heures_Cours + Mat.nb_heures_Tds + Mat.nb_heures_Tps);
	END IF;
END;
$$ LANGUAGE plpgsql;

-- liste des étudiants qui ont été toujours absents à tous ses cours
DROP FUNCTION IF EXISTS getAlwaysAbsentInAll( VARCHAR(20), INT );
CREATE OR REPLACE FUNCTION getAlwaysAbsentInAll( param_immatriculation VARCHAR(20), param_justification INT ) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite ) AS $$
BEGIN
	IF param_justification = 0 THEN
	      RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation FROM (SELECT Q.ine, COUNT(*) FROM ( SELECT Q.ine, Mat.id_matiere, Mat.nom matiere, (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps) total_heures, Q.nb_absences FROM Matiere Mat INNER JOIN ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.enseignant = param_immatriculation AND S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND A.credibilite = 0 GROUP BY A.ine, A.matiere ) AS Q ON Q.matiere = Mat.id_matiere WHERE Q.nb_absences = (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps)) AS Q GROUP BY Q.ine) AS Q, Etudiant E WHERE E.ine = Q.ine AND Q.count = (SELECT COUNT(*) nb_matieres_enseignees FROM Matiere Mat WHERE Mat.responsable = param_immatriculation);
	ELSIF param_justification = 1 THEN
	      RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation FROM (SELECT Q.ine, COUNT(*) FROM ( SELECT Q.ine, Mat.id_matiere, Mat.nom matiere, (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps) total_heures, Q.nb_absences FROM Matiere Mat INNER JOIN ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.enseignant = param_immatriculation AND S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND A.credibilite > 0 GROUP BY A.ine, A.matiere ) AS Q ON Q.matiere = Mat.id_matiere WHERE Q.nb_absences = (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps)) AS Q GROUP BY Q.ine) AS Q, Etudiant E WHERE E.ine = Q.ine AND Q.count = (SELECT COUNT(*) nb_matieres_enseignees FROM Matiere Mat WHERE Mat.responsable = param_immatriculation);
	ELSE
	    RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation FROM (SELECT Q.ine, COUNT(*) FROM ( SELECT Q.ine, Mat.id_matiere, Mat.nom matiere, (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps) total_heures, Q.nb_absences FROM Matiere Mat INNER JOIN ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.enseignant = param_immatriculation AND S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA GROUP BY A.ine, A.matiere ) AS Q ON Q.matiere = Mat.id_matiere WHERE Q.nb_absences = (Mat.nb_heures_cours + Mat.nb_heures_tds + Mat.nb_heures_tps)) AS Q GROUP BY Q.ine) AS Q, Etudiant E WHERE E.ine = Q.ine AND Q.count = (SELECT COUNT(*) nb_matieres_enseignees FROM Matiere Mat WHERE Mat.responsable = param_immatriculation);
	END IF;
END;
$$ LANGUAGE plpgsql;

-- liste des étudiants ayant OPERATOR X absences dans l'un de ses cours
DROP FUNCTION IF EXISTS getOPERATORXAbsenceByCourse( VARCHAR(20), INT);
CREATE OR REPLACE FUNCTION getOPERATORXAbsenceByCourse( param_immatriculation VARCHAR(20), param_justification INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite, matiere VARCHAR(30), nb_absences BIGINT ) AS $$
BEGIN
	IF param_justification = 0 THEN
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Mat.nom matiere, Q.nb_absences FROM Etudiant E, ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE A.id_seance = S.id_seance AND A.matiere = S.matiere AND A.isA = S.isA AND S.enseignant = param_immatriculation AND A.credibilite = 0 GROUP BY A.ine, A.matiere ) AS Q, Matiere Mat WHERE E.ine = Q.ine AND Mat.id_matiere = Q.matiere;
	ELSIF param_justification = 1 THEN
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Mat.nom matiere, Q.nb_absences FROM Etudiant E, ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE A.id_seance = S.id_seance AND A.matiere = S.matiere AND A.isA = S.isA AND S.enseignant = param_immatriculation AND A.credibilite > 0 GROUP BY A.ine, A.matiere ) AS Q, Matiere Mat WHERE E.ine = Q.ine AND Mat.id_matiere = Q.matiere;
	ELSE
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Mat.nom matiere, Q.nb_absences FROM Etudiant E, ( SELECT A.ine, A.matiere, COUNT(*) nb_absences FROM Absence A, Seance S WHERE A.id_seance = S.id_seance AND A.matiere = S.matiere AND A.isA = S.isA AND S.enseignant = param_immatriculation GROUP BY A.ine, A.matiere ) AS Q, Matiere Mat WHERE E.ine = Q.ine AND Mat.id_matiere = Q.matiere;
	END IF;
END;	
$$ LANGUAGE plpgsql;

-- liste des étudiants qui ont OPERATOR X absences non justifiées pour chaque cours.
DROP FUNCTION IF EXISTS getOPERATORXAbsenceInAll( VARCHAR(20), INT );
CREATE OR REPLACE FUNCTION getOPERATORXAbsenceInAll( param_immatriculation VARCHAR(20), param_justification INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite, nb_absences BIGINT ) AS $$
BEGIN
	IF param_justification = 0 THEN
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Q.nb_absences FROM Etudiant E, (SELECT A.ine, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND S.enseignant = param_immatriculation AND A.credibilite = 0 GROUP BY A.ine) AS Q WHERE E.ine = Q.ine;
	ELSIF param_justification = 1 THEN
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Q.nb_absences FROM Etudiant E, (SELECT A.ine, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND S.enseignant = param_immatriculation AND A.credibilite > 0 GROUP BY A.ine) AS Q WHERE E.ine = Q.ine;
	ELSE
	   RETURN QUERY SELECT Q.ine, E.nom, E.prenom, E.affectation, Q.nb_absences FROM Etudiant E, (SELECT A.ine, COUNT(*) nb_absences FROM Absence A, Seance S WHERE S.id_seance = A.id_seance AND S.matiere = A.matiere AND S.isA = A.isA AND S.enseignant = param_immatriculation GROUP BY A.ine) AS Q WHERE E.ine = Q.ine;
	END IF;
END;
$$ LANGUAGE plpgsql;

-- liste des étudiants qui ne se sont jamais absentés à tous ses cours
DROP FUNCTION IF EXISTS getNeverAbsentForAll( VARCHAR(20) );
CREATE OR REPLACE FUNCTION getNeverAbsentForAll( param_immatriculation VARCHAR(20) ) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite) AS $$
BEGIN
	RETURN QUERY SELECT DISTINCT ON (Q1.ine) Q1.ine, Q1.nom, Q1.prenom, Q1.affectation FROM ( SELECT E.ine, E.nom, E.prenom, E.affectation, unnest(E.setcourses) matiere FROM Etudiant E, ( SELECT E.ine FROM Etudiant E EXCEPT (SELECT DISTINCT ON (A.ine) A.ine FROM Absence A) ) AS Q WHERE E.ine = Q.ine ) AS Q1 INNER JOIN (SELECT DISTINCT ON (S.matiere) S.matiere FROM Seance S WHERE S.enseignant = param_immatriculation) AS Q2 ON Q1.matiere = Q2.matiere;
END;
$$ LANGUAGE plpgsql;

-- recupere la liste des matieres pour un prof en particulier
-- sera utilise dans l'interface prof pour afficher la liste des matieres auxquelles il participe
DROP FUNCTION IF EXISTS getListMatieresEnseignant(VARCHAR(20));
CREATE OR REPLACE FUNCTION getListMatieresEnseignant(immatriculation_teacher VARCHAR(20)) RETURNS TABLE(id_matiere INT, matiere VARCHAR(50)) AS $$
BEGIN
	RETURN QUERY SELECT DISTINCT ON(mat.id_matiere) mat.id_matiere, mat.nom FROM Matiere mat, Seance s WHERE mat.id_matiere = s.matiere AND s.enseignant = immatriculation_teacher;
END;
$$ LANGUAGE plpgsql;

-- recupere la liste des etudiants pour une matiere
DROP FUNCTION IF EXISTS getListStudents(INT);
CREATE OR REPLACE FUNCTION getListStudents(id_matiere INT) RETURNS TABLE( ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), affectation Specialite ) AS $$
BEGIN
	RETURN QUERY SELECT E.ine, E.nom, E.prenom, E.affectation FROM Etudiant E WHERE id_matiere = ANY (setCourses);
END;
$$ LANGUAGE plpgsql;

-- recupere la liste des séances pour une matiere et un enseignant donnés
DROP FUNCTION IF EXISTS getListSeances( INT, VARCHAR(20) );
CREATE OR REPLACE FUNCTION getListSeances(param_id_matiere INT, param_immatriculation VARCHAR(20)) RETURNS TABLE( id_seance INT, salle VARCHAR(30), isA Type_Seance, heure TIMESTAMP ) AS $$
BEGIN
	RETURN QUERY SELECT Se.id_seance, Sa.nom salle, Se.isA, date_trunc('minute', Se.heure) heure FROM Seance Se, Salle Sa WHERE Se.matiere = param_id_matiere AND Sa.id_salle = Se.salle AND Se.enseignant = param_immatriculation;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM getInformationsEnseignant('jp.dupont@gmail.com');
SELECT * FROM getListMatieresEnseignant('AZERTY123456');
SELECT * FROM getListSeances(45, 'AZERTY123456');
SELECT * FROM getListStudents(33);

SELECT * FROM getNeverAbsentIn(45);
SELECT * FROM getAlwaysAbsentForEverything(45, 1);
SELECT * FROM getAlwaysAbsentInAll('AZERTY123456', 1);
SELECT * FROM getNeverAbsentForAll('AZERTY123456');

SELECT * FROM getOPERATORXAbsenceIn(33, 2);
SELECT * FROM getOPERATORXAbsenceByCourse('AZERTY123456', 2);
SELECT * FROM getOPERATORXAbsenceInAll('AZERTY123456', 2);

